import os
from quant_researcher.quant.datasource_fetch.crypto_api.coingecko import CoinGeckoAPI
from quant_researcher.quant.project_tool.time_tool import date_shifter
import time
import pandas as pd
from utils import df_into_db


## 从coingecko获取主流衍生品交易所衍生品交易额历史数据 ###
DATA_DIR = f'E:\\指标数据'  # 用于存放测试数据
file_path = os.path.join(DATA_DIR, r'trading_data')
cg = CoinGeckoAPI()

# deri_exchange_id_list = ['binance', 'okex', 'bybit', 'ftx', 'kucoin', 'crypto_com', 'huobi', 'gate', 'bitfinex', 'kraken', 'bitmex']
deri_exchange_id_list = ['binance', 'okex', 'bybit', 'kucoin', 'crypto_com', 'huobi', 'gate', 'bitfinex', 'kraken', 'bitmex']
# deri_exchange_id_list = ['binance', 'okex']
deri_exchange_volume_df_list = []
# deri_exchange_oi_df_list = []
file_name = os.path.join(file_path, f'cex_total_deri_amount_each_exchange_coingecko')
if os.path.exists(f'{file_name}.xlsx'):
    days = 30
else:
    days = 365
for deri_exchange_id in deri_exchange_id_list:
    print(f'开始获取{deri_exchange_id}衍生品交易额数据')
    deri_exchange_data = cg.get_derivative_exchanges_volume_by_id(id=deri_exchange_id, days=days)
    deri_exchange_volume = deri_exchange_data['amount']
    print(f'{deri_exchange_id}衍生品交易额数据获取成功')
    deri_exchange_volume = deri_exchange_volume[~deri_exchange_volume.index.duplicated(keep="first")]
    deri_exchange_volume[deri_exchange_volume > 100 * deri_exchange_volume.median()] = None  # 剔除掉异常值
    deri_exchange_volume.name = deri_exchange_id
    deri_exchange_volume_df_list.append(deri_exchange_volume)

    # deri_exchange_oi = deri_exchange_data['open_interest']
    # deri_exchange_oi = deri_exchange_oi[~deri_exchange_oi.index.duplicated(keep="first")]
    # deri_exchange_oi.name = deri_exchange_id
    # deri_exchange_oi_df_list.append(deri_exchange_oi)
    time.sleep(10)
deri_exchange_volume_df = pd.concat(deri_exchange_volume_df_list, axis=1)
hist_deri_exchange_volume_df = pd.read_excel(f'{file_name}.xlsx', index_col='Unnamed: 0')
previous_date = date_shifter(deri_exchange_volume_df.index[0], step='days', how_many=-1)
deri_exchange_volume_df = pd.concat([hist_deri_exchange_volume_df.loc[:previous_date, ], deri_exchange_volume_df.iloc[:-1, ]])  # 最新一天数据为当天，数据还不完整
# deri_exchange_volume_df.to_excel(f'{file_name}.xlsx')


deri_exchange_volume_df.fillna(0, inplace=True)
deri_exchange_volume_df = deri_exchange_volume_df.reset_index(drop=False)
deri_exchange_volume_df.rename(columns={'index': 'date'}, inplace=True)
df_into_db(deri_exchange_volume_df, db_name='trading_data', table_name='cex_total_deri_amount_each_exchange_coingecko')